package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.Pagination;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.fmc.FMCWorkCharge;
import com.iplant.aps.service.po.fmc.FMCWorkspace;
import com.iplant.aps.service.po.wms.WMSLineEdgeLibrary;
import com.iplant.aps.service.po.wms.WMSPickDemand;
import com.iplant.aps.service.po.wms.WMSPickDemandItem;
import com.iplant.aps.service.utils.Configuration;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.CoreServiceImpl;
import com.iplant.aps.serviceimpl.LFSServiceImpl;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import com.iplant.aps.utils.Constants;
import com.iplant.aps.utils.aps.ExcelUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class WMSPickDemandDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSPickDemandDAO.class);

    private static WMSPickDemandDAO Instance = null;

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSPickDemand wWMSPickDemand, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSPickDemand == null)
                return 0;

            String wSQL = "";
            if (wWMSPickDemand.getID() <= 0) {
                wSQL = MessageFormat.format(
                        "INSERT INTO {0}.wms_pickdemand(Factory,OrderType,DemandNo,ExpectTime1,ExpectTime2,"
                                + "MonitorNo,Monitor,ProductID,ProductNo,LineID,LineName,CustomerID,CustomerName,"
                                + "CustomerCode,OrderID,PartNo,PartID,PartName,PartCode,DeliveryMan,DeliveryNo," +
                                "DeliveryTime,"
                                + "ReceiveMan,ReceiveNo,ReceiveTime,CreateID,Creator,CreateTime,Status,SendStatus,"
                                + "SendDesc,WBSNo,SorterID,Sorter,SorterNo,SorterTime,DeliveryID,ReceiveID,"
                                + "WMSDemandNo,Remark,TechChangeID,TechChangeNo,SourceType,ShortageIdentification," +
                                "OriginalOrderNo,SourceID,SourceCode,WaveNumber,ParentDemandNo,IsFinish," +
                                "IsFinanceAgree,WorkSpaceID) "
                                + "VALUES(:Factory,:OrderType,:DemandNo,:ExpectTime1,:ExpectTime2,:MonitorNo,"
                                + ":Monitor,:ProductID,:ProductNo,:LineID,:LineName,:CustomerID,:CustomerName,"
                                + ":CustomerCode,:OrderID,:PartNo,:PartID,:PartName,:PartCode,:DeliveryMan,:DeliveryNo,"
                                + ":DeliveryTime,:ReceiveMan,:ReceiveNo,:ReceiveTime,:CreateID,:Creator,:CreateTime," +
                                ":Status,"
                                + ":SendStatus,:SendDesc,:WBSNo,:SorterID,:Sorter,:SorterNo,:SorterTime,"
                                + ":DeliveryID,:ReceiveID,:WMSDemandNo,:Remark,:TechChangeID,:TechChangeNo," +
                                ":SourceType,:ShortageIdentification,:OriginalOrderNo,:SourceID,:SourceCode," +
                                ":WaveNumber,:ParentDemandNo,:IsFinish,:IsFinanceAgree,:WorkSpaceID);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format(
                        "UPDATE {0}.wms_pickdemand SET Factory = :Factory,OrderType = :OrderType,DemandNo = :DemandNo,"
                                + "ExpectTime1 = :ExpectTime1,ExpectTime2 = :ExpectTime2,MonitorNo = :MonitorNo,"
                                + "Monitor = :Monitor,ProductID = :ProductID,ProductNo = :ProductNo,LineID = :LineID,"
                                + "LineName = :LineName,CustomerID = :CustomerID,CustomerName = :CustomerName,"
                                + "CustomerCode = :CustomerCode,OrderID=:OrderID,PartNo = :PartNo,PartID = :PartID," +
                                "PartName = :PartName,"
                                + "PartCode = :PartCode,DeliveryMan = :DeliveryMan,DeliveryNo = :DeliveryNo,"
                                + "DeliveryTime = :DeliveryTime,ReceiveMan = :ReceiveMan,ReceiveNo = :ReceiveNo,"
                                + "ReceiveTime = :ReceiveTime,CreateID = :CreateID,Creator = :Creator,CreateTime = " +
                                ":CreateTime,"
                                + "Status = :Status,SendStatus=:SendStatus,SendDesc=:SendDesc,WBSNo=:WBSNo," +
                                "SorterID=:SorterID,"
                                + "Sorter=:Sorter,SorterNo=:SorterNo,SorterTime=:SorterTime,DeliveryID=:DeliveryID,"
                                + "ReceiveID=:ReceiveID,WMSDemandNo=:WMSDemandNo,Remark=:Remark," +
                                "TechChangeID=:TechChangeID,"
                                + "TechChangeNo=:TechChangeNo,SourceType=:SourceType,"
                                + "ShortageIdentification=:ShortageIdentification,OriginalOrderNo=:OriginalOrderNo," +
                                "SourceID=:SourceID,SourceCode=:SourceCode,ParentDemandNo=:ParentDemandNo," +
                                "IsFinish=:IsFinish,IsFinanceAgree=:IsFinanceAgree,WorkSpaceID=:WorkSpaceID " +
                                "WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSPickDemand.ID);
            wParamMap.put("Factory", wWMSPickDemand.Factory);
            wParamMap.put("OrderType", wWMSPickDemand.OrderType);
            wParamMap.put("DemandNo", wWMSPickDemand.DemandNo);
            wParamMap.put("ExpectTime1", wWMSPickDemand.ExpectTime1);
            wParamMap.put("ExpectTime2", wWMSPickDemand.ExpectTime2);
            wParamMap.put("MonitorNo", wWMSPickDemand.MonitorNo);
            wParamMap.put("Monitor", wWMSPickDemand.Monitor);
            wParamMap.put("ProductID", wWMSPickDemand.ProductID);
            wParamMap.put("ProductNo", wWMSPickDemand.ProductNo);
            wParamMap.put("LineID", wWMSPickDemand.LineID);
            wParamMap.put("LineName", wWMSPickDemand.LineName);
            wParamMap.put("CustomerID", wWMSPickDemand.CustomerID);
            wParamMap.put("CustomerName", wWMSPickDemand.CustomerName);
            wParamMap.put("CustomerCode", wWMSPickDemand.CustomerCode);
            wParamMap.put("OrderID", wWMSPickDemand.OrderID);
            wParamMap.put("PartNo", wWMSPickDemand.PartNo);
            wParamMap.put("PartID", wWMSPickDemand.PartID);
            wParamMap.put("PartName", wWMSPickDemand.PartName);
            wParamMap.put("PartCode", wWMSPickDemand.PartCode);
            wParamMap.put("DeliveryMan", wWMSPickDemand.DeliveryMan);
            wParamMap.put("DeliveryNo", wWMSPickDemand.DeliveryNo);
            wParamMap.put("DeliveryTime", wWMSPickDemand.DeliveryTime);
            wParamMap.put("ReceiveMan", wWMSPickDemand.ReceiveMan);
            wParamMap.put("ReceiveNo", wWMSPickDemand.ReceiveNo);
            wParamMap.put("ReceiveTime", wWMSPickDemand.ReceiveTime);
            wParamMap.put("CreateID", wWMSPickDemand.CreateID);
            wParamMap.put("Creator", wWMSPickDemand.Creator);
            wParamMap.put("CreateTime", wWMSPickDemand.CreateTime);
            wParamMap.put("Status", wWMSPickDemand.Status);
            wParamMap.put("SendStatus", wWMSPickDemand.SendStatus);
            wParamMap.put("SendDesc", wWMSPickDemand.SendDesc);
            wParamMap.put("WBSNo", wWMSPickDemand.WBSNo);
            wParamMap.put("SorterID", wWMSPickDemand.SorterID);
            wParamMap.put("Sorter", wWMSPickDemand.Sorter);
            wParamMap.put("SorterNo", wWMSPickDemand.SorterNo);
            wParamMap.put("SorterTime", wWMSPickDemand.SorterTime);
            wParamMap.put("DeliveryID", wWMSPickDemand.DeliveryID);
            wParamMap.put("ReceiveID", wWMSPickDemand.ReceiveID);
            wParamMap.put("WMSDemandNo", wWMSPickDemand.WMSDemandNo);
            wParamMap.put("Remark", wWMSPickDemand.Remark);
            wParamMap.put("TechChangeID", wWMSPickDemand.TechChangeID);
            wParamMap.put("TechChangeNo", wWMSPickDemand.TechChangeNo);
            wParamMap.put("SourceType", wWMSPickDemand.SourceType);
            wParamMap.put("ShortageIdentification", wWMSPickDemand.ShortageIdentification);
            wParamMap.put("OriginalOrderNo", wWMSPickDemand.OriginalOrderNo);
            wParamMap.put("SourceID", wWMSPickDemand.SourceID);
            wParamMap.put("SourceCode", wWMSPickDemand.SourceCode);
            wParamMap.put("WaveNumber", wWMSPickDemand.WaveNumber);
            wParamMap.put("ParentDemandNo", wWMSPickDemand.ParentDemandNo);
            wParamMap.put("IsFinish", wWMSPickDemand.IsFinish);
            wParamMap.put("IsFinanceAgree", wWMSPickDemand.IsFinanceAgree);
            wParamMap.put("WorkSpaceID", wWMSPickDemand.WorkSpaceID);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSPickDemand.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSPickDemand.setID(wResult);
            } else {
                wResult = wWMSPickDemand.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSPickDemand> wList,
            OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSPickDemand wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_pickdemand WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSPickDemand SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSPickDemand wResult = new WMSPickDemand();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSPickDemand> wList = SelectList(wLoginUser, wID, "", "", -1, -1, -1, "", -1, -1, null, null, null,
                    wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
            wResult.ItemList = WMSPickDemandItemDAO.getInstance().SelectList(wLoginUser, -1, wResult.ID, -1, "", -1,
                    wErrorCode);
            wResult.ItemList.forEach(p -> {
                p.DemandNo = wList.get(0).DemandNo;
                p.ProductNo = wList.get(0).ProductNo;
                p.LineName = wList.get(0).LineName;
                p.CustomerName = wList.get(0).CustomerName;
                p.PartName = wList.get(0).PartName;
                p.PartNo = wList.get(0).PartNo;
            });
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSPickDemand SelectByIDWithoutSub(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSPickDemand wResult = new WMSPickDemand();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSPickDemand> wList = SelectList(wLoginUser, wID, "", "", -1, -1, -1, "", -1, -1, null, null, null,
                    wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSPickDemand> SelectList(BMSEmployee wLoginUser, int wID, String wOrderType, String wDemandNo,
            int wProductID, int wLineID, int wCustomerID, String wOrderID, int wPartID,
            int wStatus, Calendar wStartTime,
            Calendar wEndTime, List<Integer> wIDList, OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            if (wIDList == null) {
                wIDList = new ArrayList<>();
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.Status OrderStatus,t2.Name WorkSpaceName,t2.Code WorkSpaceCode FROM {0}.wms_pickdemand t "
                    + " left join {0}.oms_order t1 on t.OrderID=t1.ID "
                    + " left join {0}.fmc_workspace t2 on t.WorkSpaceID=t2.ID "
                    + "WHERE  1=1  "
                    + "and ( :wID <= 0 or :wID = t.ID ) "
                    + "and ( :wOrderType is null or :wOrderType = '''' or :wOrderType = t.OrderType ) "
                    + "and ( :wDemandNo is null or :wDemandNo = '''' or t.DemandNo like ''%{1}%'' or " +
                    "t.OriginalOrderNo like ''%{1}%'' ) "
                    + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                    "t.CreateTime ) "
                    + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                    "t.CreateTime ) "
                    + "and ( :wProductID <= 0 or :wProductID = t.ProductID ) "
                    + "and ( :wLineID <= 0 or :wLineID = t.LineID ) "
                    + "and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                    + "and ( :wIDs is null or :wIDs = '''' or t.ID in ({2})) "
                    + "and ( :wOrderID is null or :wOrderID = '''' or t.OrderID in ({3})) " + "and ( :wPartID" +
                    " " +
                    "<= 0 or :wPartID = " +
                    "t.PartID ) "
                    + "and ( :wStatus <= 0 or :wStatus = t.Status );", wInstance.Result, wDemandNo,
                    wIDList.size() > 0 ? StringUtils.Join(",", wIDList) : "0",
                    StringUtils.isEmpty(wOrderID) ? "0" : wOrderID);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wOrderType", wOrderType);
            wParamMap.put("wDemandNo", wDemandNo);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wIDs", StringUtils.Join(",", wIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat wDateSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wLoginUser, wErrorCode, wResultList, wQueryResult, wSDF, wDateSDF, wBaseTime1);

            wResultList.removeIf(p -> p.Status < 0);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }


    /**
     * 根据ID列表查询配送单
     */
    public List<WMSPickDemand> SelectListByIDList(BMSEmployee wLoginUser, List<Integer> wIDList, OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wIDList == null || wIDList.size() <= 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.Status OrderStatus,t2.Name WorkSpaceName,t2.Code WorkSpaceCode FROM {0}.wms_pickdemand t "
                    + " left join {0}.oms_order t1 on t.OrderID=t1.ID "
                    + " left join {0}.fmc_workspace t2 on t.WorkSpaceID=t2.ID "
                    + "WHERE  1=1  "
                    + "and ( t.ID in ({1}) );", wInstance.Result,
                    StringUtils.Join(",", wIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat wDateSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wLoginUser, wErrorCode, wResultList, wQueryResult, wSDF, wDateSDF, wBaseTime1);

            wResultList.removeIf(p -> p.Status < 0);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }


    /**
     * 条件查询集合,增加需求单号|波次号|MES单号、来源、推送状态、预计送达日期等
     */
    public List<WMSPickDemand> SelectListPro(BMSEmployee wLoginUser, int wID, String wOrderType, String wDemandNo,
            int wProductID, int wLineID, int wCustomerID, String wOrderID, int wPartID,
            int wStatus, Calendar wStartTime,
            Calendar wEndTime, List<Integer> wIDList,
            int wSourceType, int wSendStatus, Calendar wPreSendStartDate,
            Calendar wPreSendEndDate, Pagination wPagination, String wSourceTypeNot,
            String wOrderTypeNot, int wIsFinish,
            OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }
            if (wPreSendStartDate == null) {
                wPreSendStartDate = wBaseTime;
            }
            if (wPreSendEndDate == null) {
                wPreSendEndDate = wBaseTime;
            }
            if (wPreSendStartDate.compareTo(wPreSendEndDate) > 0) {
                return wResultList;
            }

            if (wIDList == null) {
                wIDList = new ArrayList<>();
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.Status OrderStatus,t2.Name WorkSpaceName,t2.Code WorkSpaceCode FROM {0}.wms_pickdemand t "
                    + " left join {0}.oms_order t1 on t.OrderID=t1.ID "
                    + " left join {0}.fmc_workspace t2 on t.WorkSpaceID=t2.ID "
                    + "WHERE  1=1  "
                    + "and ( :wID <= 0 or :wID = t.ID ) "
                    + "and ( :wSourceType <= 0 or :wSourceType = t.SourceType ) "
                    + "and ( :wSendStatus <= 0 or :wSendStatus = t.SendStatus ) "
                    + "and ( :wOrderType is null or :wOrderType = '''' or :wOrderType = t.OrderType ) "
                    + "and ( :wDemandNo is null or :wDemandNo = '''' or t.DemandNo like ''%{1}%'' or " +
                    "t.OriginalOrderNo like ''%{1}%'' or t.WaveNumber like ''%{1}%'' or t.SourceCode like " +
                    "''%{1}%'' ) "
                    + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                    "t.CreateTime ) "
                    + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                    "t.CreateTime ) "
                    + "and ( :wPreSendStartDate <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or " +
                    ":wPreSendStartDate <=  " +
                    "t.ExpectTime1 ) "
                    + "and ( :wPreSendEndDate <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or " +
                    ":wPreSendEndDate >=  " +
                    "t.ExpectTime1 ) "
                    + "and ( :wProductID <= 0 or :wProductID = t.ProductID ) "
                    + "and ( :wIsFinish <= 0 or (:wIsFinish=1 and t1.Status>=5) or (:wIsFinish=2 and t1" +
                    ".Status<5) ) "
                    + "and ( :wLineID <= 0 or :wLineID = t.LineID ) "
                    + "and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                    + "and ( :wIDs is null or :wIDs = '''' or t.ID in ({2})) "
                    + "and ( :wSourceTypeNot is null or :wSourceTypeNot = '''' or t.SourceType not in ({4})) "
                    + "and ( :wOrderTypeNot is null or :wOrderTypeNot = '''' or t.OrderType not in ({5})) "
                    + "and ( :wOrderID is null or :wOrderID = '''' or t.OrderID in ({3})) " + "and ( :wPartID" +
                    " " +
                    "<= 0 or :wPartID = " +
                    "t.PartID ) "
                    + "and ( :wStatus <= 0 or :wStatus = t.Status ) and t.Status>=0 ", wInstance.Result,
                    wDemandNo,
                    wIDList.size() > 0 ? StringUtils.Join(",", wIDList) : "0",
                    StringUtils.isEmpty(wOrderID) ? "0" : wOrderID,
                    StringUtils.isEmpty(wSourceTypeNot) ? "0" : wSourceTypeNot,
                    StringUtils.isEmpty(wOrderTypeNot) ? "0" : wOrderTypeNot);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wOrderType", wOrderType);
            wParamMap.put("wDemandNo", wDemandNo);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wIDs", StringUtils.Join(",", wIDList));
            wParamMap.put("wSourceType", wSourceType);
            wParamMap.put("wSendStatus", wSendStatus);
            wParamMap.put("wPreSendStartDate", wPreSendStartDate);
            wParamMap.put("wPreSendEndDate", wPreSendEndDate);
            wParamMap.put("wSourceTypeNot", wSourceTypeNot);
            wParamMap.put("wOrderTypeNot", wOrderTypeNot);
            wParamMap.put("wIsFinish", wIsFinish);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap, wPagination);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat wDateSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wLoginUser, wErrorCode, wResultList, wQueryResult, wSDF, wDateSDF, wBaseTime1);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 条件查询集合,增加需求单号|波次号|MES单号、来源、推送状态、预计送达日期等
     */
    public List<Integer> SelectExportIDList(BMSEmployee wLoginUser, int wID, String wOrderType, String wDemandNo,
            int wProductID, int wLineID, int wCustomerID, String wOrderID,
            int wPartID,
            int wStatus, Calendar wStartTime,
            Calendar wEndTime, List<Integer> wIDList,
            int wSourceType, int wSendStatus, Calendar wPreSendStartDate,
            Calendar wPreSendEndDate, String wSourceTypeNot,
            String wOrderTypeNot, int wIsFinish,
            OutResult<Integer> wErrorCode) {
        List<Integer> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }
            if (wPreSendStartDate == null) {
                wPreSendStartDate = wBaseTime;
            }
            if (wPreSendEndDate == null) {
                wPreSendEndDate = wBaseTime;
            }
            if (wPreSendStartDate.compareTo(wPreSendEndDate) > 0) {
                return wResultList;
            }

            if (wIDList == null) {
                wIDList = new ArrayList<>();
            }

            String wSQL = MessageFormat.format("SELECT distinct t.ID FROM {0}.wms_pickdemand t "
                    + " left join {0}.oms_order t1 on t.OrderID=t1.ID "
                    + "WHERE  1=1  "
                    + "and ( :wID <= 0 or :wID = t.ID ) "
                    + "and ( :wSourceType <= 0 or :wSourceType = t.SourceType ) "
                    + "and ( :wSendStatus <= 0 or :wSendStatus = t.SendStatus ) "
                    + "and ( :wOrderType is null or :wOrderType = '''' or :wOrderType = t.OrderType ) "
                    + "and ( :wDemandNo is null or :wDemandNo = '''' or t.DemandNo like ''%{1}%'' or " +
                    "t.OriginalOrderNo like ''%{1}%'' or t.WaveNumber like ''%{1}%'' or t.SourceCode like " +
                    "''%{1}%'' ) "
                    + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                    "t.CreateTime ) "
                    + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                    "t.CreateTime ) "
                    + "and ( :wPreSendStartDate <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or " +
                    ":wPreSendStartDate <=  " +
                    "t.ExpectTime1 ) "
                    + "and ( :wPreSendEndDate <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or " +
                    ":wPreSendEndDate >=  " +
                    "t.ExpectTime1 ) "
                    + "and ( :wProductID <= 0 or :wProductID = t.ProductID ) "
                    + "and ( :wIsFinish <= 0 or (:wIsFinish=1 and t1.Status>=5) or (:wIsFinish=2 and t1" +
                    ".Status<5) ) "
                    + "and ( :wLineID <= 0 or :wLineID = t.LineID ) "
                    + "and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                    + "and ( :wIDs is null or :wIDs = '''' or t.ID in ({2})) "
                    + "and ( :wSourceTypeNot is null or :wSourceTypeNot = '''' or t.SourceType not in ({4})) "
                    + "and ( :wOrderTypeNot is null or :wOrderTypeNot = '''' or t.OrderType not in ({5})) "
                    + "and ( :wOrderID is null or :wOrderID = '''' or t.OrderID in ({3})) " + "and ( :wPartID" +
                    " " +
                    "<= 0 or :wPartID = " +
                    "t.PartID ) "
                    + "and ( :wStatus <= 0 or :wStatus = t.Status ) and t.Status>=0 ", wInstance.Result,
                    wDemandNo,
                    wIDList.size() > 0 ? StringUtils.Join(",", wIDList) : "0",
                    StringUtils.isEmpty(wOrderID) ? "0" : wOrderID,
                    StringUtils.isEmpty(wSourceTypeNot) ? "0" : wSourceTypeNot,
                    StringUtils.isEmpty(wOrderTypeNot) ? "0" : wOrderTypeNot);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wOrderType", wOrderType);
            wParamMap.put("wDemandNo", wDemandNo);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wIDs", StringUtils.Join(",", wIDList));
            wParamMap.put("wSourceType", wSourceType);
            wParamMap.put("wSendStatus", wSendStatus);
            wParamMap.put("wPreSendStartDate", wPreSendStartDate);
            wParamMap.put("wPreSendEndDate", wPreSendEndDate);
            wParamMap.put("wSourceTypeNot", wSourceTypeNot);
            wParamMap.put("wOrderTypeNot", wOrderTypeNot);
            wParamMap.put("wIsFinish", wIsFinish);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResultList.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 条件查询集合
     */
    public List<WMSPickDemand> SelectListByWaveNumber(BMSEmployee wLoginUser, String wWaveNumber,
            OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (StringUtils.isEmpty(wWaveNumber)) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.Status OrderStatus FROM {0}.wms_pickdemand t "
                    + " left join {0}.oms_order t1 on t.OrderID=t1.ID "
                    + "WHERE  1=1  "
                    + "and ( :wWaveNumber is null or :wWaveNumber = '''' or t.WaveNumber = :wWaveNumber );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wWaveNumber", wWaveNumber);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat wDateSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wLoginUser, wErrorCode, wResultList, wQueryResult, wSDF, wDateSDF, wBaseTime1);

            wResultList.removeIf(p -> p.Status < 0);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 赋值
     */
    private void SetValue(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode, List<WMSPickDemand> wResultList,
            List<Map<String, Object>> wQueryResult, SimpleDateFormat wSDF, SimpleDateFormat wDateSDF,
            Calendar wBaseTime1) {
        try {
            boolean wIsActiveRowHandOver = Configuration.readConfigString("IsActiveRowHandOver", "config/config")
                    .equals("1");
            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemand wItem = new WMSPickDemand();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.Factory = StringUtils.parseString(wReader.get("Factory"));
                wItem.OrderType = StringUtils.parseString(wReader.get("OrderType"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.ExpectTime1 = StringUtils.parseCalendar(wReader.get("ExpectTime1"));
                wItem.ExpectTime2 = StringUtils.parseCalendar(wReader.get("ExpectTime2"));
                wItem.MonitorNo = StringUtils.parseString(wReader.get("MonitorNo"));
                wItem.Monitor = StringUtils.parseString(wReader.get("Monitor"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                if (StringUtils.isEmpty(wItem.ProductNo)) {
                    wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                }
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                if (StringUtils.isEmpty(wItem.LineName)) {
                    wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                }
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wItem.CustomerCode = StringUtils.parseString(wReader.get("CustomerCode"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.DeliveryMan = StringUtils.parseString(wReader.get("DeliveryMan"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.TechChangeID = StringUtils.parseInt(wReader.get("TechChangeID"));
                wItem.TechChangeNo = StringUtils.parseString(wReader.get("TechChangeNo"));
                wItem.SourceType = StringUtils.parseInt(wReader.get("SourceType"));
                wItem.ShortageIdentification = StringUtils.parseInt(wReader.get("ShortageIdentification"));
                wItem.OriginalOrderNo = StringUtils.parseString(wReader.get("OriginalOrderNo"));

                wItem.DeliveryID = StringUtils.parseInt(wReader.get("DeliveryID"));
                wItem.DeliveryNo = StringUtils.parseString(wReader.get("DeliveryNo"));
                wItem.IsFinish = StringUtils.parseInt(wReader.get("IsFinish"));

                // 完工车和订单状态绑定
                if (wReader.containsKey("OrderStatus")) {
                    int wOrderStatus = StringUtils.parseInt(wReader.get("OrderStatus"));
                    if (wOrderStatus >= 5) {
                        wItem.IsFinish = 1;
                    } else {
                        wItem.IsFinish = 0;
                    }
                }

                wItem.IsFinanceAgree = StringUtils.parseInt(wReader.get("IsFinanceAgree"));

                //台位信息
                wItem.WorkSpaceID = StringUtils.parseInt(wReader.get("WorkSpaceID"));
                wItem.WorkSpaceName = StringUtils.parseString(wReader.get("WorkSpaceName"));
                wItem.WorkSpaceCode = StringUtils.parseString(wReader.get("WorkSpaceCode"));

                if (StringUtils.isEmpty(wItem.DeliveryNo) && wItem.PartID > 0) {
                    wItem.DeliveryMan = GetMonitorNamesByPart(wLoginUser, wItem.PartID,
                            wErrorCode);
                    // 来源为`偶换件`的需要增加偶换件的发起人
                    if (wItem.SourceType == WMSPickDemandSourceType.OHJ.getValue() && wItem.ReceiveID > 0) {
                        String wUpflowName = APSConstans.GetBMSEmployeeName(wItem.ReceiveID);
                        if (!wItem.DeliveryMan.contains(wUpflowName) && StringUtils.isNotEmpty(wUpflowName)) {
                            wItem.DeliveryMan += "," + wUpflowName;
                        }
                    }
                }

                wItem.DeliveryTime = StringUtils.parseCalendar(wReader.get("DeliveryTime"));
                wItem.ReceiveMan = StringUtils.parseString(wReader.get("ReceiveMan"));

                // if (StringUtils.isEmpty(wItem.ReceiveMan) && wItem.PartID > 0) {
                // wItem.ReceiveMan = GetMonitorNamesByPart(wLoginUser, wItem.PartID,
                // wErrorCode);
                // }

                wItem.ReceiveID = StringUtils.parseInt(wReader.get("ReceiveID"));
                wItem.ReceiveNo = StringUtils.parseString(wReader.get("ReceiveNo"));
                wItem.ReceiveTime = StringUtils.parseCalendar(wReader.get("ReceiveTime"));
                wItem.CreateID = StringUtils.parseInt(wReader.get("CreateID"));
                wItem.Creator = StringUtils.parseString(wReader.get("Creator"));
                if (wItem.CreateID == -100)
                    wItem.Creator = "MES系统";

                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.SendStatus = StringUtils.parseInt(wReader.get("SendStatus"));
                wItem.SendDesc = StringUtils.parseString(wReader.get("SendDesc"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));

                wItem.SorterID = StringUtils.parseInt(wReader.get("SorterID"));
                wItem.SourceID = StringUtils.parseInt(wReader.get("SourceID"));
                wItem.SourceCode = StringUtils.parseString(wReader.get("SourceCode"));
                wItem.Sorter = StringUtils.parseString(wReader.get("Sorter"));
                wItem.SorterNo = StringUtils.parseString(wReader.get("SorterNo"));
                wItem.SorterTime = StringUtils.parseCalendar(wReader.get("SorterTime"));
                wItem.ReceiveID = StringUtils.parseInt(wReader.get("ReceiveID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.WMSDemandNo = StringUtils.parseString(wReader.get("WMSDemandNo"));
                wItem.WaveNumber = StringUtils.parseString(wReader.get("WaveNumber"));
                wItem.ParentDemandNo = StringUtils.parseString(wReader.get("ParentDemandNo"));
                if (wItem.WaveNumber.equals("*")) {
                    wItem.WaveNumber = "";
                }

                wItem.SorterTimeText = wItem.SorterTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.SorterTime.getTime())
                        : "";

                // 辅助属性
                wItem.ExpectTime1Text = wSDF.format(wItem.ExpectTime1.getTime());
                wItem.ExpectTime2Text = wSDF.format(wItem.ExpectTime2.getTime());
                wItem.DeliveryTimeText = wItem.DeliveryTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.DeliveryTime.getTime())
                        : "";
                wItem.OrderTypeText = WMSOrderType.getEnumType(StringUtils.parseInt(wItem.OrderType)).getLable();
                wItem.ReceiveTimeText = wItem.ReceiveTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.ReceiveTime.getTime())
                        : "";
                wItem.CreateTimeText = wSDF.format(wItem.CreateTime.getTime());
                wItem.StatusText = WMSPickDemandStatus.getEnumType(wItem.Status).getLable();
                if (wIsActiveRowHandOver) {
                    wItem.StatusText = WMSPickDemandStatusNew.getEnumType(wItem.Status).getLable();
                }
                wItem.SendStatusText = wItem.SendStatus == 1 ? "成功" : wItem.SendStatus == 2 ? "失败" : "暂未推送";

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 获取班组长
     */
    private String GetMonitorNamesByPart(BMSEmployee wLoginUser, int wPartID, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            List<FMCWorkCharge> wChargeList = APSConstans.GetFMCWorkChargeList().stream()
                    .filter(p -> p.StationID == wPartID && p.Active == 1).collect(Collectors.toList());
            List<BMSEmployee> wUserList = APSConstans.GetBMSEmployeeList().values().stream()
                    .filter(p -> wChargeList.stream().anyMatch(q -> q.ClassID == p.DepartmentID)
                            && APSConstans.GetBMSPosition(p.Position).DutyID == 1 && p.Active == 1)
                    .collect(Collectors.toList());
            wResult = StringUtils.Join(",",
                    wUserList.stream().map(p -> p.Name).distinct().collect(Collectors.toList()));
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /*
     * 根据工位获取班组名称，逗号隔开
     *
     * @param wLoginUser 登录信息
     * 
     * @param partID 工位
     * 
     * @param wErrorCode 错误信息
     * 
     * @return 班组名称，逗号隔开
     */
    // private String GetClassNames(BMSEmployee wLoginUser, int partID,
    // OutResult<Integer> wErrorCode) {
    // String wResult = "";
    // try {
    // ServiceResult<String> wInstance =
    // this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
    // wLoginUser.getID(), 0);
    // wErrorCode.set(wInstance.ErrorCode);
    // if (wErrorCode.Result != 0) {
    // return wResult;
    // }

    // String wSQL = StringUtils.Format(
    // "SELECT Name FROM {0}.bms_department where ID in (SELECT ClassID "
    // + "FROM iplantmes.bms_workcharge where StationID=:StationID and Active=1);",
    // wInstance.Result);

    // Map<String, Object> wParamMap = new HashMap<>();

    // wParamMap.put("StationID", partID);

    // wSQL = this.DMLChange(wSQL);

    // List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL,
    // wParamMap);

    // List<String> wNames = new ArrayList<>();
    // for (Map<String, Object> wReader : wQueryResult) {
    // String wName = StringUtils.parseString(wReader.get("Name"));
    // wNames.add(wName);
    // }
    // wResult = StringUtils.Join(",", wNames);
    // } catch (Exception ex) {
    // logger.error(ex.toString());
    // }
    // return wResult;
    // }

    /**
     * 获取最新的配送单号
     */
    public String GetNewCode(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            wResult = LFSServiceImpl.getInstance().WMS_CreateNewDemandNo(wLoginUser).Info(String.class);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取最新的委外送修单号
     */
    public String GetNewCode_Out(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            wResult = LFSServiceImpl.getInstance().WMS_CreateNewDemandNoOut(wLoginUser).Info(String.class);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    private WMSPickDemandDAO() {
        super();
    }

    public static WMSPickDemandDAO getInstance() {
        if (Instance == null)
            Instance = new WMSPickDemandDAO();
        return Instance;
    }

    public BMSEmployee GetMonitorByPart(BMSEmployee wLoginUser, int wPartID, OutResult<Integer> wErrorCode) {
        BMSEmployee wResult = new BMSEmployee();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "select t1.Name,t1.LoginID from iplantmes.mbs_user t1,iplantmes.bms_position t2 "
                            + "where t1.DepartmentID in (SELECT ClassID FROM iplantmes.bms_workcharge "
                            + "where StationID=:StationID and Active=1) and t1.Position=t2.ID and t2.DutyID=1 AND t1" +
                            ".Active=1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("StationID", wPartID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.Name = StringUtils.parseString(wReader.get("Name"));
                wResult.LoginID = StringUtils.parseString(wReader.get("LoginID"));
                return wResult;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取满足条件的自动推送ID集合
     */
    public List<Integer> SelectAutoList(BMSEmployee wLoginUser, Calendar wToday, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT ID FROM {0}.wms_pickdemand " + "where Status=1 and :wToday>=ExpectTime1 and SendStatus=0;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wToday", wToday);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wItem = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询已办数据
     */
    public List<WMSPickDemand> SelectTaskDoneList(BMSEmployee wLoginUser, Calendar wStartTime, Calendar wEndTime,
            OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.*,t1.Status OrderStatus FROM {0}.wms_pickdemand t "
                    + " left join {0}.oms_order t1 on t.OrderID=t1.ID "
                    + "where (t.SorterID=:LoginID or t.DeliveryID=:LoginID or t.ReceiveID=:LoginID ) "
                    + "and t.CreateTime>=:wStartTime and t.CreateTime<=:wEndTime;", wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("LoginID", wLoginUser.ID);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat wDateSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            SetValue(wLoginUser, wErrorCode, wResult, wQueryResult, wSDF, wDateSDF, wBaseTime1);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> SelectPickFinishedIDList(BMSEmployee wLoginUser, Calendar wSTime, Calendar wETime,
            OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.wms_pickdemand WHERE STATUS=3 OR " +
                    "(STATUS=4 AND DeliveryTime>:wSTime AND DeliveryTime<:wETime);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wSTime", wSTime);
            wParamMap.put("wETime", wETime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> SelectSendFinishedIDList(BMSEmployee wLoginUser, Calendar wSTime, Calendar wETime,
            OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.wms_pickdemand WHERE STATUS=4 OR " +
                    "(STATUS=5 AND ReceiveTime>:wSTime AND ReceiveTime<:wETime);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wSTime", wSTime);
            wParamMap.put("wETime", wETime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> GetPickDemandIDList(BMSEmployee wLoginUser,
            OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.wms_pickdemand WHERE sourcetype=1",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public int GetPickDemandItemCount(BMSEmployee wLoginUser, int wDemandID,
            OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) Number FROM {0}.wms_pickdemanditem WHERE " +
                    "DemandID=:DemandID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("DemandID", wDemandID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("Number"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public int GetMaterialPlanItemCount(BMSEmployee wLoginUser, WMSPickDemand wPickDemand,
            OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) Number FROM {0}.mrp_materialPlan WHERE orderid=:orderid" +
                    " AND PartID=:PartID AND ACTIVE=1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("orderid", wPickDemand.OrderID);
            wParamMap.put("PartID", wPickDemand.PartID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("Number"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取导出的数据
     */
    public MyExcelSheet GetMyExcelSheet() {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            wResult.SheetName = "需求计划不一致的配送单";
            wResult.TitleName = "需求计划不一致的配送单";
            wResult.HeaderList = new ArrayList<>();
            wResult.HeaderList.add("配送单号");
            wResult.HeaderList.add("车号");
            wResult.HeaderList.add("工位");
            wResult.HeaderList.add("需求计划数");
            wResult.HeaderList.add("配送单数");

            wResult.DataList = new ArrayList<>();

            OutResult<Integer> wErrorCode = new OutResult<>(0);
            List<Integer> wIDList = WMSPickDemandDAO.getInstance().GetPickDemandIDList(BaseDAO.SysAdmin, wErrorCode);
            List<WMSPickDemand> wList = WMSPickDemandDAO.getInstance().SelectList(BaseDAO.SysAdmin, -1, "",
                    "", -1, -1,
                    -1, "", -1, -1, null, null, wIDList, wErrorCode);
            for (WMSPickDemand wPickDemand : wList) {
                int wPickDemandItemCount = this.GetPickDemandItemCount(BaseDAO.SysAdmin, wPickDemand.ID, wErrorCode);
                int wMaterialPlanItemCount = this.GetMaterialPlanItemCount(BaseDAO.SysAdmin, wPickDemand, wErrorCode);
                if (wPickDemandItemCount != wMaterialPlanItemCount) {
                    List<String> wDataList = new ArrayList<>();
                    wDataList.add(wPickDemand.DemandNo);
                    wDataList.add(wPickDemand.PartNo);
                    wDataList.add(wPickDemand.PartName);
                    wDataList.add(String.valueOf(wMaterialPlanItemCount));
                    wDataList.add(String.valueOf(wPickDemandItemCount));
                    wResult.DataList.add(wDataList);
                }
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return wResult;
    }

    public String WMS_ExportExcel(BMSEmployee wLoginUser, WMSPickDemand wWMSPickDemand, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            SimpleDateFormat wSimpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
            String wFileName = StringUtils.Format("委外拆修入库单_{0}.xls",
                    wSimpleDateFormat.format(Calendar.getInstance().getTime()));
            String wDirePath = StringUtils.Format("{0}static/export/",
                    Constants.getConfigPath().replace("config/", ""));
            File wDirFile = new File(wDirePath);
            if (!wDirFile.exists()) {
                wDirFile.mkdirs();
            }
            String wFilePath = StringUtils.Format("{0}{1}", wDirePath, wFileName);
            File wNewFile = new File(wFilePath);
            wNewFile.createNewFile();

            FileOutputStream wFileOutputStream = new FileOutputStream(wNewFile);
            ExcelUtil.WMS_WriteOutPickDemand(wLoginUser, wWMSPickDemand, wFileOutputStream);
            wResult = StringUtils.Format("/{0}/export/{1}",
                    Configuration.readConfigString("project.name", "application"), wFileName);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public boolean IsSendDemand(BMSEmployee wLoginUser, int wOrderID, int wPartPointID, OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return false;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) FQTY FROM {0}.wms_pickdemanditem t1,{0}" +
                    ".wms_pickdemand t2 WHERE t1.DemandID=t2.ID AND t1.PartPointID=:PartPointID AND t2" +
                    ".OrderID=:OrderID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("PartPointID", wPartPointID);
            wParamMap.put("OrderID", wOrderID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wFQTY = StringUtils.parseInt(wReader.get("FQTY"));
                if (wFQTY > 0) {
                    wResult = true;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     *是否已提反冲领料需求
     */
    public boolean IsSendDemandByRecoil(BMSEmployee wLoginUser, int wOrderID, int wPartPointID, OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return false;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) FQTY FROM {0}.wms_pickdemanditem t1,{0}" +
                            ".wms_pickdemand t2 WHERE t1.DemandID=t2.ID AND t1.PartPointID=:PartPointID AND t2" +
                            ".OrderID=:OrderID and t2.OrderType = ''110'' and t2.SourceType = 1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("PartPointID", wPartPointID);
            wParamMap.put("OrderID", wOrderID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wFQTY = StringUtils.parseInt(wReader.get("FQTY"));
                if (wFQTY > 0) {
                    wResult = true;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<WMSPickDemand> GetRepeatList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t1.ID,t1.DemandNo,t1.CreateTime,t1.Status " +
                    "FROM {0}.wms_pickdemand t1,{0}.wms_pickdemand t2 WHERE t1.CreateTime<t2" +
                    ".CreateTime AND t1.demandno=t2.demandno AND t1.ID != t2.ID AND t1.demandno !='''' ORDER " +
                    "BY" +
                    " t1.demandno ASC,t1.createtime ASC;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemand wItem = new WMSPickDemand();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));

                wResult.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> WMS_QueryListByPartPoint(BMSEmployee wLoginUser, int wOrderID, int wPartID, int wPartPointID,
            OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t.DemandID FROM {0}.`wms_pickdemanditem` t " +
                    "LEFT JOIN {0}.`wms_pickdemand` t1 ON t.DemandID=t1.ID WHERE t1.OrderID=:OrderID " +
                    "AND t.PartID=:PartID AND t.PartPointID=:PartPointID; ",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("PartID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wDemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wResult.add(wDemandID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询所有已完工，待分拣的配送单ID集合
     */
    public List<Integer> WMS_QueryCancelAllFinishedIDList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t1.ID FROM {0}.`wms_pickdemand` t1 LEFT JOIN {0}.oms_order t2 " +
                    "ON t1.OrderID=t2.ID WHERE t1.status=2 AND t2.status>=5;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<WMSPickDemand> WMS_QueryRecoilLineEdgeSource(BMSEmployee wLoginUser,
            WMSLineEdgeLibrary wWMSLineEdgeLibrary,
            OutResult<Integer> wErrorCode) {
        List<WMSPickDemand> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.ID,t2.DemandNo,t2.PartNo,t2.PartName,t.PickedNumber FROM " +
                    "{0}.`wms_pickdemanditemstock` t   " +
                    "LEFT JOIN {0}.`wms_pickdemanditem` t1 ON t.TaskID= t1.ID  " +
                    "LEFT JOIN {0}.`wms_pickdemand` t2 ON t1.DemandID=t2.ID  " +
                    "LEFT JOIN {0}.lfs_workareastation t3 ON t2.PartID=t3.StationID  " +
                    "WHERE t.BatchNumber=:BatchNumber AND t1.MaterialID=:MaterialID AND t2.Status in (3,4,5) " +
                    "AND t3" +
                    ".WorkAreaID=:WorkAreaID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("BatchNumber", wWMSLineEdgeLibrary.BatchNumber);
            wParamMap.put("MaterialID", wWMSLineEdgeLibrary.MaterialID);
            wParamMap.put("WorkAreaID", wWMSLineEdgeLibrary.AreaID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            List<WMSPickDemand> wList = new ArrayList<>();
            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemand wItem = new WMSPickDemand();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.ProductNo = wItem.PartNo.split("#")[0];
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.SubMaterialNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));

                wList.add(wItem);
            }

            // 按照单号去重
            List<WMSPickDemand> wItemList = wList.stream()
                    .collect(Collectors.collectingAndThen(Collectors.toCollection(
                            () -> new TreeSet<>(Comparator.comparing(WMSPickDemand::getDemandNo))),
                            ArrayList::new));
            for (WMSPickDemand wWMSPickDemand : wItemList) {
                wWMSPickDemand.SubMaterialNumber = wList.stream()
                        .filter(p -> p.DemandNo.equals(wWMSPickDemand.DemandNo)).mapToDouble(p -> p.SubMaterialNumber)
                        .sum();
                wWMSPickDemand.SubMaterialNumber = new BigDecimal(wWMSPickDemand.SubMaterialNumber)
                        .setScale(2, RoundingMode.HALF_UP).doubleValue();
                wResult.add(wWMSPickDemand);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> SelectNotCloseIDList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.wms_pickdemand WHERE STATUS IN (2,3,7);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 主单 标记完工关闭，
     * 明细行，已配送、已取消 不变， 保存 改为已取消，
     * 待分拣、拣货中、分拣完成 标记完工关闭 且传wms取消，修改成功 改为已取消，修改失败 保持完工关闭 状态，直至修改成功 改为已取消
     */
    public void CompletedClose(BMSEmployee wLoginUser, WMSPickDemand wWMSPickDemand, OutResult<Integer> wErrorCode) {
        try {
            wWMSPickDemand = this.SelectByID(wLoginUser, wWMSPickDemand.ID, wErrorCode);
            // ①主单 标记完工关闭，
            wWMSPickDemand.Status = WMSPickDemandStatusNew.FinishClosed.getValue();
            this.Update(wLoginUser, wWMSPickDemand, wErrorCode);
            // ②明细行，已配送、已取消 不变
            for (WMSPickDemandItem wWMSPickDemandItem : wWMSPickDemand.ItemList) {
                // 需求为0的，直接改状态并跳过
                if (wWMSPickDemandItem.FQTY <= 0) {
                    wWMSPickDemandItem.Status = WMSPickDemandItemStatusNew.Canceled.getValue();
                    WMSPickDemandItemDAO.getInstance().Update(wLoginUser, wWMSPickDemandItem, wErrorCode);
                    continue;
                }

                switch (WMSPickDemandItemStatusNew.getEnumType(wWMSPickDemandItem.Status)) {
                    case Saved:
                        wWMSPickDemandItem.DeleteFlag = 1;
                        wWMSPickDemandItem.Status = WMSPickDemandItemStatusNew.Canceled.getValue();
                        WMSPickDemandItemDAO.getInstance().Update(wLoginUser, wWMSPickDemandItem, wErrorCode);
                        break;
                    case Default:
                    case Sended:
                    case Picking:
                    case Picked:
                        wWMSPickDemandItem.DeleteFlag = 1;
                        wWMSPickDemandItem.Status = WMSPickDemandItemStatusNew.FinishedClose.getValue();
                        WMSPickDemandItemDAO.getInstance().Update(wLoginUser, wWMSPickDemandItem, wErrorCode);
                        // 且传wms取消 改为已取消，修改失败 保持完工关闭 状态 直至修改成功 改为已取消
                        WMSPickDemandItemDAO.getInstance().CancelToWMS(wLoginUser, wWMSPickDemand, wWMSPickDemandItem,
                                wErrorCode);
                        break;
                    case Finished:
                        // 部分分拣的情况
                        if (wWMSPickDemandItem.PickedNumber < wWMSPickDemandItem.FQTY) {
                            wWMSPickDemandItem.DeleteFlag = 1;
                            wWMSPickDemandItem.Status = WMSPickDemandItemStatusNew.FinishedClose.getValue();
                            WMSPickDemandItemDAO.getInstance().Update(wLoginUser, wWMSPickDemandItem, wErrorCode);
                            // 且传wms取消 改为已取消，修改失败 保持完工关闭 状态 直至修改成功 改为已取消
                            WMSPickDemandItemDAO.getInstance().CancelToWMS(wLoginUser, wWMSPickDemand,
                                    wWMSPickDemandItem, wErrorCode);
                        }
                        break;
                    case Canceled:
                    default:
                        break;
                }
            }
            // ③明细行 保存 改为已取消，
            // ④明细行，待分拣、拣货中、分拣完成 标记完工关闭 ，且传wms取消 改为已取消，修改失败 保持完工关闭 状态 直至修改成功 改为已取消
            // ⑤关闭配送单交接待办消息
            CoreServiceImpl.getInstance().BFC_MsgHandleTask(wLoginUser, BPMEventModule.MaterialSend.getValue(), 0,
                    BFCMessageStatus.Finished.getValue(), new ArrayList<>(Collections.singletonList(wWMSPickDemand.ID)),
                    0, 1);
            // ⑥反冲料入线边库
            // WMSServiceImpl.getInstance().WMS_IncomingLineSideLibrary_2(wLoginUser,
            // wWMSPickDemand.ID);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public boolean JudgeIsCreated(BMSEmployee wLoginUser, int wOrderID, int wPartID, OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) FQTY FROM {0}.`wms_pickdemand` WHERE " +
                    "SourceType=1 AND OrderID=:OrderID AND PartID=:PartID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("PartID", wPartID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wFQTY = StringUtils.parseInt(wReader.get("FQTY"));
                if (wFQTY > 0) {
                    return true;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public boolean JudgeIsCreatedByStepIDs(BMSEmployee wLoginUser, int wOrderID, int wPartID, String wStepIDs,
            OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) FQTY FROM {0}.wms_pickdemanditem t LEFT JOIN " +
                    "{0}.wms_pickdemand t1 ON t.demandid=t1.Id WHERE t1.orderid=:OrderID AND t1" +
                    ".partid=:PartID " +
                    "AND t.partpointid IN ({1}) AND t1.sourcetype=1;",
                    wInstance.Result, wStepIDs);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("PartID", wPartID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wFQTY = StringUtils.parseInt(wReader.get("FQTY"));
                if (wFQTY > 0) {
                    return true;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 通过来源码判断是否已创建
     */
    public boolean JudgeIsCreatedBySourceCode(BMSEmployee wLoginUser, String wSourceCode,
            OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT COUNT(*) FQTY FROM {0}.wms_pickdemand WHERE " +
                    "sourcecode=:sourcecode;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("sourcecode", wSourceCode);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wFQTY = StringUtils.parseInt(wReader.get("FQTY"));
                if (wFQTY > 0) {
                    wResult = true;
                    return wResult;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 判断物料配送单是否已完成
     */
    public boolean WMS_IsPickDemandFinished(BMSEmployee wLoginUser, String wSFCBOMTaskCode,
            OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT Status FROM {0}.wms_pickdemand WHERE SourceCode=:SourceCode LIMIT 1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();
            wParamMap.put("SourceCode", wSFCBOMTaskCode);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wStatus = StringUtils.parseInt(wReader.get("Status"));
                // 状态为4(已配送)或5(已接收)时表示已完成
                if (wStatus == WMSPickDemandStatusNew.Finished.getValue()
                        || wStatus == WMSPickDemandStatusNew.FinishClosed.getValue()) {
                    wResult = true;
                    break;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 根据订单ID和工位IDs查询配送单
     */
    public List<Integer> SelectIDListByOrderIDAndStationIDList(BMSEmployee wLoginUser, int iD,
            List<Integer> wStationIDList, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.wms_pickdemand WHERE OrderID=:OrderID AND PartID IN ({1}) AND Status not in (6) ;",
                    wInstance.Result, StringUtils.Join(",", wStationIDList));

            Map<String, Object> wParamMap = new HashMap<>();
            wParamMap.put("OrderID", iD);

            wSQL = this.DMLChange(wSQL);    

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     *根据车号获取台位信息
     */
    public FMCWorkspace FMC_GetFMCWorkspaceByPartNo(BMSEmployee wLoginUser, String partNo, OutResult<Integer> wErrorCode) {
        FMCWorkspace wResult = new FMCWorkspace();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID,Name,Code FROM {0}.`fmc_workspace` WHERE PartNo = :PartNo;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("PartNo", partNo);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.ID = StringUtils.parseInt(wReader.get("ID"));
                wResult.Name = StringUtils.parseString(wReader.get("Name"));
                wResult.Code = StringUtils.parseString(wReader.get("Code"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
